package controll;

import java.io.ObjectInputStream.GetField;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import model.BangGia;
import model.ChiTietHoaDon;
import model.Ga;
import model.Ghe;
import model.GioTau;
import model.HoaDon;
import model.LoaiGhe;
import model.NhanVien;
import model.Tau;
import model.Toa;
import model.Tuyen;

public class Mapping {

	public List<Tuyen> getListTuyen() throws Throwable {
		List<Tuyen> lstTuyen = null;
		try {
			if (ConnectData.getConnection()) {
				lstTuyen = new ArrayList<Tuyen>();
				Statement st = ConnectData.con.createStatement();
				ResultSet rs = st.executeQuery("select * from Tuyen");
				Tuyen tuyen = null;
				while (rs.next()) {
					tuyen = new Tuyen();
					tuyen.setTuyenID(rs.getLong("TuyenID"));
					tuyen.setTen(rs.getString("Ten"));
					tuyen.setHuong(rs.getString("Huong"));
					lstTuyen.add(tuyen);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list tuyen!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstTuyen;
	}
	
	/**
	 * Get list Tau
	 */
	public List<Tau> getListTau() {
		List<Tau> lstTau = null;
		try {
			if (ConnectData.getConnection()) {
				lstTau = new ArrayList<Tau>();
				Statement st = ConnectData.con.createStatement();
				ResultSet rs = st
						.executeQuery("select * from Tau");
				Tau tau = null;
				while (rs.next()) {
					tau = new Tau();
					tau.setTauID(rs.getLong("TauID"));
					tau.setTenTau(rs.getString("TenTau"));
					tau.setTuyenID(new Tuyen().getTuyenByTuyenID(rs.getLong("TuyenID")));
					lstTau.add(tau);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list tuyen!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstTau;
	}

	/**
	 * Get list Tau belong Tuyen
	 * 
	 * @param tuyen
	 */
	public List<Tau> getListTau(Tuyen tuyen) {
		List<Tau> lstTau = null;
		try {
			if (ConnectData.getConnection()) {
				lstTau = new ArrayList<Tau>();
				Statement st = ConnectData.con.createStatement();
				ResultSet rs = st
						.executeQuery("select * from Tau where TuyenID =  '"
								+ tuyen.getTuyenID() + "' ");
				Tau tau = null;
				while (rs.next()) {
					tau = new Tau();
					tau.setTauID(rs.getLong("TauID"));
					tau.setTenTau(rs.getString("TenTau"));
					tau.setTuyenID(tuyen);
					lstTau.add(tau);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list tuyen!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstTau;
	}

	/**
	 * Get list "toa" of train
	 * 
	 * @param tau
	 */
	public List<Toa> getListToa(Tau tau) {
		List<Toa> lstToa = null;
		try {
			if (ConnectData.getConnection()) {
				lstToa = new ArrayList<Toa>();
				Statement st = ConnectData.con.createStatement();
				ResultSet rs = st
						.executeQuery("select * from Toa A INNER JOIN TauToa B on A.ToaID = B.ToaID JOIN  Tau C ON B.TauID = C.TauID");
				Toa toa = null;
				while (rs.next()) {
					toa = new Toa();
					toa.setToaID(rs.getLong("ToaID"));
					toa.setTenToa(rs.getString("TenToa"));
					lstToa.add(toa);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list toa!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstToa;
	}

	/**
	 * Get list "BangGia" by tauID
	 * 
	 * @param tau
	 * @return
	 */
	public List<BangGia> getBangGiaList(Tau tau) {
		List<BangGia> lstBangGia = null;
		try {
			if (ConnectData.getConnection()) {
				lstBangGia = new ArrayList<BangGia>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from BangGia where TauID = ?");
				pst.setLong(1, tau.getTauID());
				ResultSet rs = pst.executeQuery();

				BangGia bangGia = null;
				while (rs.next()) {
					bangGia = new BangGia();
					bangGia.setBangGiaID(rs.getLong("BangGiaID"));
					bangGia.setTauID(tau);
					bangGia.setGheId((new Ghe()).getGheByGheID(rs
							.getLong("GheId")));
					bangGia.setGaDi((new Ga()).getGaByGaID(rs.getLong("GaDi")));
					bangGia.setGaDen((new Ga()).getGaByGaID(rs.getLong("GaDen")));
					bangGia.setGiaTien(rs.getLong("GiaTien"));
					lstBangGia.add(bangGia);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list BangGia!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstBangGia;
	}

	public Ghe getGheByGheID(long gheID) {
		Ghe ghe = null;
		try {
			if (ConnectData.getConnection()) {
				ghe = new Ghe();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from Ghe where Ghe.GheID = ?");
				pst.setLong(1, gheID);
				ResultSet rs = pst.executeQuery();
				ghe.setGheID(rs.getLong("GheID"));
				ghe.setLoaiGheID((new LoaiGhe()).getLoaiGheByGheID(rs
						.getLong("LoaiGheID")));
				ghe.setSoGhe(rs.getString("SoGhe"));
				ghe.setToaID((new Toa()).getToaByToaID(rs.getLong("ToaID")));
			} else {
				JOptionPane
						.showMessageDialog(null, "Have error while get Ghe!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return ghe;
	}

	public LoaiGhe getLoaiGheByGheID(long loaiGheID) {
		LoaiGhe loaiGhe = null;
		try {
			if (ConnectData.getConnection()) {
				loaiGhe = new LoaiGhe();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from LoaiGhe where LoaiGhe.LoaiGheID = ?");
				pst.setLong(1, loaiGheID);
				ResultSet rs = pst.executeQuery();
				if (rs.next()) {
					loaiGhe = new LoaiGhe();
					loaiGhe.setLoaiGheID(rs.getLong("LoaiGheID"));
					loaiGhe.setTen(rs.getString("Ten"));
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get LoaiGhe!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return loaiGhe;
	}

	public Toa getToaByToaID(long toaID) {
		Toa toa = null;
		try {
			if (ConnectData.getConnection()) {
				toa = new Toa();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from Toa where ToaID = ?");
				pst.setLong(1, toaID);
				ResultSet rs = pst.executeQuery();
				toa.setToaID(rs.getLong("ToaID"));
				toa.setTenToa(rs.getString("TenToa"));
			} else {
				JOptionPane
						.showMessageDialog(null, "Have error while get Toa!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return toa;
	}

	public Ga getGaByGaID(long gaID) {
		Ga ga = null;
		try {
			if (ConnectData.getConnection()) {
				ga = new Ga();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from Ga where GaID = ?");
				pst.setLong(1, gaID);
				ResultSet rs = pst.executeQuery();
				ga.setGaID(rs.getLong("gaID"));
				ga.setTen(rs.getString("Ten"));
				ga.setDiaChi(rs.getString("DiaChi"));
				ga.setSdt(rs.getString("SDT"));
			} else {
				JOptionPane
						.showMessageDialog(null, "Have error while get Toa!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return ga;
	}

	/**
	 * Get list "BangGia" by "ghe"
	 * 
	 * @param tau
	 * @return
	 */

	public List<BangGia> getListBangGiaByGheID(Ghe ghe) {
		List<BangGia> lstBangGia = null;
		try {
			if (ConnectData.getConnection()) {
				lstBangGia = new ArrayList<BangGia>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from BangGia where GheID = ?");
				pst.setLong(1, ghe.getGheID());
				ResultSet rs = pst.executeQuery();

				BangGia bangGia = null;
				while (rs.next()) {
					bangGia = new BangGia();
					bangGia.setBangGiaID(rs.getLong("BangGiaID"));
					bangGia.setTauID((new Tau()).getTauByTauID(rs
							.getLong("TauID")));
					bangGia.setGheId(ghe);
					bangGia.setGaDi((new Ga()).getGaByGaID(rs.getLong("GaDi")));
					bangGia.setGaDen((new Ga()).getGaByGaID(rs.getLong("GaDen")));
					bangGia.setGiaTien(rs.getLong("GiaTien"));
					lstBangGia.add(bangGia);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list BangGia!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstBangGia;
	}

	public Tau getTauByTauID(long tauID) {
		Tau tau = null;
		try {
			if (ConnectData.getConnection()) {
				tau = new Tau();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from Tau where TauID = ?");
				pst.setLong(1, tauID);
				ResultSet rs = pst.executeQuery();
				if (rs.next()) {
					tau.setTauID(rs.getLong("TauID"));
					tau.setTenTau(rs.getString("TenTau"));
					tau.setTuyenID((new Tuyen()).getTuyenByTuyenID(rs
							.getLong("TuyenID")));
				}
			} else {
				JOptionPane
						.showMessageDialog(null, "Have error while get Tau!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return tau;
	}

	public Tuyen getTuyenByTuyenID(Long tuyenID) {
		Tuyen tuyen = null;
		try {
			if (ConnectData.getConnection()) {
				tuyen = new Tuyen();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from Tuyen where TuyenID = ?");
				pst.setLong(1, tuyenID);
				ResultSet rs = pst.executeQuery();
				if (rs.next()) {
					tuyen.setTuyenID(tuyenID);
					tuyen.setTen(rs.getString("TuyenID"));
					tuyen.setHuong(rs.getString("Huong"));
				}

			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get Tuyen!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return tuyen;
	}

	/**
	 * Get list "GioTau" by station starting
	 * 
	 * @param ga
	 *            : station start
	 * @return
	 */
	public List<GioTau> getGioTauList(Ga ga) {
		List<GioTau> lstGioTau = null;
		try {
			if (ConnectData.getConnection()) {
				lstGioTau = new ArrayList<GioTau>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from GioTau where GaID = ?");
				pst.setLong(1, ga.getGaID());
				ResultSet rs = pst.executeQuery();
				GioTau gioTau = null;
				while (rs.next()) {
					gioTau = new GioTau();
					gioTau.setGioDi(rs.getDate("GioDi"));
					gioTau.setGaID(ga);
					gioTau.setGioDen(rs.getDate("GioDen"));
					gioTau.setGioID(rs.getLong("GioID"));
					gioTau.setTauID(new Tau().getTauByTauID(rs.getLong("TauID")));

					lstGioTau.add(gioTau);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list GioTau!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstGioTau;
	}

	/**
	 * Lay list gio tau theo tau
	 * 
	 * @param tau
	 * @return
	 */
	public List<GioTau> getGioTauList(Tau tau) {
		List<GioTau> lstGioTau = null;
		try {
			if (ConnectData.getConnection()) {
				lstGioTau = new ArrayList<GioTau>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from GioTau where TauID = ?");
				pst.setLong(1, tau.getTauID());
				ResultSet rs = pst.executeQuery();
				GioTau gioTau = null;
				while (rs.next()) {
					gioTau = new GioTau();
					gioTau.setGioDi(rs.getDate("GioDi"));
					gioTau.setGaID(new Ga().getGaByGaID(rs.getLong("GaID")));
					gioTau.setGioDen(rs.getDate("GioDen"));
					gioTau.setGioID(rs.getLong("GioID"));
					gioTau.setTauID(tau);

					lstGioTau.add(gioTau);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list GioTau!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstGioTau;
	}

	/**
	 * Get list ChiTietHoaDon by HoaDon
	 * 
	 * @param hoaDon
	 * @return
	 */
	public List<ChiTietHoaDon> getChiTietHoaDonList(HoaDon hoaDon) {
		List<ChiTietHoaDon> lstChiTietHoaDon = null;
		try {
			if (ConnectData.getConnection()) {
				lstChiTietHoaDon = new ArrayList<ChiTietHoaDon>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from ChiTietHoaDon where CTHDID = ?");
				pst.setLong(1, hoaDon.getHoaDonID());
				ResultSet rs = pst.executeQuery();
				ChiTietHoaDon hd = null;
				while (rs.next()) {
					hd = new ChiTietHoaDon();
					hd.setCthdid(rs.getLong("CTHDID"));
					hd.setTauID(rs.getLong("TauID"));
					hd.setGaDi((new Ga()).getGaByGaID(rs.getLong("GaDi")));
					hd.setGaDen((new Ga()).getGaByGaID(rs.getLong("GaDen")));
					hd.setGheID(new Ghe().getGheByGheID(rs.getLong("GheID")));
					hd.setNgayTauChay(rs.getDate("NgayTauChay"));
					hd.setHoaDonID(new HoaDon().getHoaDonByID(rs
							.getLong(("HoaDonID"))));
					lstChiTietHoaDon.add(hd);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list ChiTietHoaDon!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstChiTietHoaDon;
	}

	public HoaDon getHoaDonByID(Long hoaDonID) {
		HoaDon hoaDon = null;
		try {
			if (ConnectData.getConnection()) {
				hoaDon = new HoaDon();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from HoaDon where HoaDonID = ?");
				pst.setLong(1, hoaDonID);
				ResultSet rs = pst.executeQuery();
				if (rs.next()) {
					hoaDon.setDiaChi(rs.getString("DiaChi"));
					hoaDon.setHoaDonID(rs.getLong("HoaDonID"));
					hoaDon.setNhanVienID(new NhanVien().getNhanVienByID(rs
							.getLong("NhanVienID")));
					hoaDon.setScmndkh(rs.getString("SCMNDKH"));
					hoaDon.setSdt(rs.getString("SDT"));
					hoaDon.setSoTien(rs.getLong("soTien"));
					hoaDon.setTenKH(rs.getString("TenKH"));
					hoaDon.setThoiGian(rs.getDate("ThoiGian"));
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get HoaDon!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return hoaDon;
	}

	public NhanVien getNhanVienByID(long nhanVienID) {
		NhanVien nhanVien = null;
		try {
			if (ConnectData.getConnection()) {
				nhanVien = new NhanVien();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from NhanVien where NhanVienID = ?");
				pst.setLong(1, nhanVienID);
				ResultSet rs = pst.executeQuery();
				if (rs.next()) {
					nhanVien.setDiaChi(rs.getString("DiaChi"));
					nhanVien.setEmail(rs.getString("Email"));
					nhanVien.setNhanVienID(nhanVienID);
					nhanVien.setPassWord(rs.getString("passWord"));
					nhanVien.setSdt(rs.getString("SDT"));
					nhanVien.setTen(rs.getString("Ten"));
					nhanVien.setUserName(rs.getString("UserName"));
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get NhanVien!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return nhanVien;
	}

	/**
	 * Get list ChiTietHoaDon by Ghe
	 * 
	 * @param hoaDon
	 * @return
	 */
	public List<ChiTietHoaDon> getChiTietHoaDonList(Ghe ghe) {
		List<ChiTietHoaDon> lstChiTietHoaDon = null;
		try {
			if (ConnectData.getConnection()) {
				lstChiTietHoaDon = new ArrayList<ChiTietHoaDon>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from ChiTietHoaDon where GheID = ?");
				pst.setLong(1, ghe.getGheID());
				ResultSet rs = pst.executeQuery();
				ChiTietHoaDon hd = null;
				while (rs.next()) {
					hd = new ChiTietHoaDon();
					hd.setCthdid(rs.getLong("CTHDID"));
					hd.setTauID(rs.getLong("TauID"));
					hd.setGaDi((new Ga()).getGaByGaID(rs.getLong("GaDi")));
					hd.setGaDen((new Ga()).getGaByGaID(rs.getLong("GaDen")));
					hd.setGheID(new Ghe().getGheByGheID(rs.getLong("GheID")));
					hd.setNgayTauChay(rs.getDate("NgayTauChay"));
					hd.setHoaDonID(new HoaDon().getHoaDonByID(rs
							.getLong(("HoaDonID"))));
					lstChiTietHoaDon.add(hd);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list ChiTietHoaDon!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstChiTietHoaDon;
	}

	/**
	 * Get list BangGia by GaDi
	 * 
	 * @param ga
	 * @return
	 */
	public List<BangGia> getListBangGiaDi(Ga gaDi) {
		List<BangGia> lstBangGia = null;
		try {
			if (ConnectData.getConnection()) {
				lstBangGia = new ArrayList<BangGia>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from BangGia where GaDi = ?");
				pst.setLong(1, gaDi.getGaID());
				ResultSet rs = pst.executeQuery();

				BangGia bangGia = null;
				while (rs.next()) {
					bangGia = new BangGia();
					bangGia.setBangGiaID(rs.getLong("BangGiaID"));
					bangGia.setTauID(new Tau().getTauByTauID(rs
							.getLong("TauID")));
					bangGia.setGheId((new Ghe()).getGheByGheID(rs
							.getLong("GheId")));
					bangGia.setGaDi(gaDi);
					bangGia.setGaDen((new Ga()).getGaByGaID(rs.getLong("GaDen")));
					bangGia.setGiaTien(rs.getLong("GiaTien"));
					lstBangGia.add(bangGia);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list BangGia!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstBangGia;
	}

	/**
	 * Get list BangGia by GaDen
	 * 
	 * @param ga
	 * @return
	 */
	public List<BangGia> getListBangGiaDen(Ga gaDen) {
		List<BangGia> lstBangGia = null;
		try {
			if (ConnectData.getConnection()) {
				lstBangGia = new ArrayList<BangGia>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from BangGia where GaDen = ?");
				pst.setLong(1, gaDen.getGaID());
				ResultSet rs = pst.executeQuery();

				BangGia bangGia = null;
				while (rs.next()) {
					bangGia = new BangGia();
					bangGia.setBangGiaID(rs.getLong("BangGiaID"));
					bangGia.setTauID(new Tau().getTauByTauID(rs
							.getLong("TauID")));
					bangGia.setGheId((new Ghe()).getGheByGheID(rs
							.getLong("GheId")));
					bangGia.setGaDi((new Ga()).getGaByGaID(rs.getLong("GaDi")));
					bangGia.setGaDen((new Ga()).getGaByGaID(rs.getLong("GaDen")));
					bangGia.setGiaTien(rs.getLong("GiaTien"));
					lstBangGia.add(bangGia);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list BangGia!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstBangGia;
	}

	/**
	 * Get list ChiTietHoaDon starting
	 * 
	 * @param ga
	 * @return
	 */
	public List<ChiTietHoaDon> getChiTietHoaDonDiList(Ga ga) {
		List<ChiTietHoaDon> lstChiTietHoaDon = null;
		try {
			if (ConnectData.getConnection()) {
				lstChiTietHoaDon = new ArrayList<ChiTietHoaDon>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from ChiTietHoaDon where GaDi = ?");
				pst.setLong(1, ga.getGaID());
				ResultSet rs = pst.executeQuery();
				ChiTietHoaDon hd = null;
				while (rs.next()) {
					hd = new ChiTietHoaDon();
					hd.setCthdid(rs.getLong("CTHDID"));
					hd.setTauID(rs.getLong("TauID"));
					hd.setGaDi((new Ga()).getGaByGaID(rs.getLong("GaDi")));
					hd.setGaDen((new Ga()).getGaByGaID(rs.getLong("GaDen")));
					hd.setGheID(new Ghe().getGheByGheID(rs.getLong("GheID")));
					hd.setNgayTauChay(rs.getDate("NgayTauChay"));
					hd.setHoaDonID(new HoaDon().getHoaDonByID(rs
							.getLong(("HoaDonID"))));
					lstChiTietHoaDon.add(hd);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list ChiTietHoaDon!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstChiTietHoaDon;
	}

	/**
	 * Get list ChiTietHoaSon by station where arrive
	 * 
	 * @param ga
	 *            : station where start
	 * @return
	 */
	public List<ChiTietHoaDon> getChiTietHoaDonDenList(Ga ga) {
		List<ChiTietHoaDon> lstChiTietHoaDon = null;
		try {
			if (ConnectData.getConnection()) {
				lstChiTietHoaDon = new ArrayList<ChiTietHoaDon>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from ChiTietHoaDon where GaDen = ?");
				pst.setLong(1, ga.getGaID());
				ResultSet rs = pst.executeQuery();
				ChiTietHoaDon hd = null;
				while (rs.next()) {
					hd = new ChiTietHoaDon();
					hd.setCthdid(rs.getLong("CTHDID"));
					hd.setTauID(rs.getLong("TauID"));
					hd.setGaDi((new Ga()).getGaByGaID(rs.getLong("GaDi")));
					hd.setGaDen((new Ga()).getGaByGaID(rs.getLong("GaDen")));
					hd.setGheID(new Ghe().getGheByGheID(rs.getLong("GheID")));
					hd.setNgayTauChay(rs.getDate("NgayTauChay"));
					hd.setHoaDonID(new HoaDon().getHoaDonByID(rs
							.getLong(("HoaDonID"))));
					lstChiTietHoaDon.add(hd);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list ChiTietHoaDon!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstChiTietHoaDon;
	}

	/**
	 * Get list of seat by toa
	 * 
	 * @param toa
	 * @return
	 */
	public List<Ghe> getGheList(Toa toa) {
		List<Ghe> lstGhe = null;
		try {
			if (ConnectData.getConnection()) {
				lstGhe = new ArrayList<Ghe>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from Ghe where ToaID = ?");
				pst.setLong(1, toa.getToaID());
				ResultSet rs = pst.executeQuery();
				Ghe ghe = null;
				while (rs.next()) {
					ghe.setGheID(rs.getLong("GheID"));
					ghe.setLoaiGheID((new LoaiGhe()).getLoaiGheByGheID(rs
							.getLong("LoaiGheID")));
					ghe.setSoGhe(rs.getString("SoGhe"));
					ghe.setToaID((new Toa()).getToaByToaID(rs.getLong("ToaID")));
					lstGhe.add(ghe);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list Ghe by toaID!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstGhe;
	}

	/**
	 * Get list seat by seat category
	 * 
	 * @param loaiGhe
	 * @return
	 */
	public List<Ghe> getGheList(LoaiGhe loaiGhe) {
		List<Ghe> lstGhe = null;
		try {
			if (ConnectData.getConnection()) {
				lstGhe = new ArrayList<Ghe>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from Ghe where LoaiGheID = ?");
				pst.setLong(1, loaiGhe.getLoaiGheID());
				ResultSet rs = pst.executeQuery();
				Ghe ghe = null;
				while (rs.next()) {
					ghe.setGheID(rs.getLong("GheID"));
					ghe.setLoaiGheID((new LoaiGhe()).getLoaiGheByGheID(rs
							.getLong("LoaiGheID")));
					ghe.setSoGhe(rs.getString("SoGhe"));
					ghe.setToaID((new Toa()).getToaByToaID(rs.getLong("ToaID")));
					lstGhe.add(ghe);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list Ghe by LoaiGheID!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstGhe;
	}

	/**
	 * Get list HoaDon by NhanVienID
	 * 
	 * @param nhanVien
	 * @return
	 */
	public List<HoaDon> getHoaDonList(NhanVien nhanVien) {
		List<HoaDon> lstHoaDon = null;
		try {
			if (ConnectData.getConnection()) {
				lstHoaDon = new ArrayList<HoaDon>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from HoaDon where NhanVienID = ?");
				pst.setLong(1, nhanVien.getNhanVienID());
				ResultSet rs = pst.executeQuery();
				HoaDon hoaDon = null;
				while (rs.next()) {
					hoaDon.setDiaChi(rs.getString("DiaChi"));
					hoaDon.setHoaDonID(rs.getLong("HoaDonID"));
					hoaDon.setNhanVienID(new NhanVien().getNhanVienByID(rs
							.getLong("NhanVienID")));
					hoaDon.setScmndkh(rs.getString("SCMNDKH"));
					hoaDon.setSdt(rs.getString("SDT"));
					hoaDon.setSoTien(rs.getLong("soTien"));
					hoaDon.setTenKH(rs.getString("TenKH"));
					hoaDon.setThoiGian(rs.getDate("ThoiGian"));
					lstHoaDon.add(hoaDon);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list 	HoaDon by NhanVienID!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstHoaDon;
	}

	/**
	 * Get list of Tau by Toa
	 * 
	 * @param toa
	 * @return
	 */
	public List<Tau> getTauList(Toa toa) {
		List<Tau> lstTau = null;
		try {
			if (ConnectData.getConnection()) {
				lstTau = new ArrayList<Tau>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from Tau where ToaID = ?");
				pst.setLong(1, toa.getToaID());
				ResultSet rs = pst.executeQuery();
				Tau tau = null;
				while (rs.next()) {
					tau = new Tau();
					tau.setTauID(rs.getLong("TauID"));
					tau.setTenTau(rs.getString("TenTau"));
					tau.setTuyenID((new Tuyen()).getTuyenByTuyenID(rs
							.getLong("TuyenID")));
					lstTau.add(tau);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list tuyen!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstTau;
	}

	/**
	 * Get list ChiTietHoaDon by Tau, Ghe and Ngay
	 * 
	 * @param tau
	 * @param ghe
	 * @param ngay
	 * @return
	 */
	public List<ChiTietHoaDon> getListCTHD(Tau tau, Ghe ghe, Date ngay) {
		List<ChiTietHoaDon> lstChiTietHoaDon = null;
		try {
			if (ConnectData.getConnection()) {
				lstChiTietHoaDon = new ArrayList<ChiTietHoaDon>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from ChiTietHoaDon where TauID = ? and GheID = ? and NgayTauChay = ?");
				pst.setLong(1, tau.getTauID());
				pst.setLong(2, ghe.getGheID());
				pst.setDate(3, ngay);
				ResultSet rs = pst.executeQuery();
				ChiTietHoaDon hd = null;
				while (rs.next()) {
					hd = new ChiTietHoaDon();
					hd.setCthdid(rs.getLong("CTHDID"));
					hd.setTauID(rs.getLong("TauID"));
					hd.setGaDi((new Ga()).getGaByGaID(rs.getLong("GaDi")));
					hd.setGaDen((new Ga()).getGaByGaID(rs.getLong("GaDen")));
					hd.setGheID(new Ghe().getGheByGheID(rs.getLong("GheID")));
					hd.setNgayTauChay(rs.getDate("NgayTauChay"));
					hd.setHoaDonID(new HoaDon().getHoaDonByID(rs
							.getLong(("HoaDonID"))));
					lstChiTietHoaDon.add(hd);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list ChiTietHoaDon!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstChiTietHoaDon;
	}

	/**
	 * Get list BangGia by Ghe
	 * @param ghe : the seat will be get list cost
	 * @return
	 */
	public List<BangGia> getBangGiaList(Ghe ghe) {
		List<BangGia> lstBangGia = null;
		try {
			if (ConnectData.getConnection()) {
				lstBangGia = new ArrayList<BangGia>();
				PreparedStatement pst = ConnectData.con
						.prepareStatement("select * from BangGia where GheID = ?");
				pst.setLong(1, ghe.getGheID());
				ResultSet rs = pst.executeQuery();

				BangGia bangGia = null;
				while (rs.next()) {
					bangGia = new BangGia();
					bangGia.setBangGiaID(rs.getLong("BangGiaID"));
					bangGia.setTauID(new Tau().getTauByTauID(rs.getLong("TauID")));
					bangGia.setGheId((new Ghe()).getGheByGheID(rs
							.getLong("GheId")));
					bangGia.setGaDi((new Ga()).getGaByGaID(rs.getLong("GaDi")));
					bangGia.setGaDen((new Ga()).getGaByGaID(rs.getLong("GaDen")));
					bangGia.setGiaTien(rs.getLong("GiaTien"));
					lstBangGia.add(bangGia);
				}
			} else {
				JOptionPane.showMessageDialog(null,
						"Have error while get list BangGia!");
			}
		} catch (Throwable ex) {
			// Catch exception and throw outside
		}
		return lstBangGia;
	}

	

}
